﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.IO;

namespace ass5Vinyl.CONTROLLER
{
    class AdoHelper
    {
        //get the connection string from the app.config file

        
       //***** static string connectionString = Search_Engine_Part1.Properties.Settings.Default.Engine_DBConnectionString;

        //declare the db connection
        public static string connection = global::ass5Vinyl.Properties.Settings.Default.vinylConnectionString;
        public static SqlConnection con = new SqlConnection(connection);
        /// <summary>
        /// To Execute queries which returns result set (table / relation)
        /// </summary>
        /// <param name="query">the query string</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string query)
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand(query, con);
                SqlDataAdapter tableAdapter = new SqlDataAdapter(command);
                DataTable dt = new DataTable();
                tableAdapter.Fill(dt);
               
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                
            }
        }

        
      
        /// <summary>
        /// To Execute update / insert / delete queries
        /// </summary>
        /// <param name="query">the query string</param>
        public static void ExecuteNonQuery(string query)
        {
            try
            {
                con.Open();
                SqlCommand command = new SqlCommand(query, con);

                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

    }

    class DataQueries
    {

        public static DataTable GetAllTable(string table)
        {
            string query = "Select * from dbo." + table;
            return AdoHelper.ExecuteDataTable(query);
        }


        public static void DeleteTable(string table)
        {
            string query = "Truncate Table dbo." + table;
            AdoHelper.ExecuteNonQuery(query);
        }

       
        public static DataTable PersonExist(string Id,string table)
        {
            string query = "Select *  From dbo." + table + " WHERE "+table+".eId ='"+Id+"'";
            return AdoHelper.ExecuteDataTable(query);
        }
        public static DataTable customerExist(string Id, string table)
        {
            string query = "Select cId as count From dbo." + table + " WHERE " + table + ".cId ='" + Id + "'";
            return AdoHelper.ExecuteDataTable(query);
        }

        public static void InsertEmployee(Employee e, string table)
        {
            string query = " INSERT INTO dbo." + table + " (eId,eName,eAddress,ePhone,eSalary)"+ " VALUES ('"+e.eId+"','"+e.eName+"','"+e.eAdderess+"','"+e.ePhone+"','"+e.eSalary+"');";
            AdoHelper.ExecuteNonQuery(query);
        }

        public static void InsertCostumer(Customer c, string table)
        {
            string query = " INSERT INTO dbo." + table + " (cId,cName,cLastName,cAddress,cPhone,cBirthDay)" + " VALUES ('" + c.cId + "','" + c.cName + "','" + c.CLastName + "','" + c.cAdderess + "','" + c.cPhone + "','" + c.cBirthDay + "');";
            AdoHelper.ExecuteNonQuery(query);
        }


    }
}
